﻿namespace OleDbDAL.Systems
{
    using DBUtility;
    using IDal.Systems;
    using Model.Systems;
    using System;
    using System.Data;
    using System.Text;
    using WinFormSession;

    public class UserApp : IUser
    {
        private string dbname = "Systems";

        public void Add(UserEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into T_User(");
            builder.Append("ChainNum,RoleId,DeptId,LoginName,UserPass,UserName,IdCode,UserPic,CreateTime,IsUseLogin,IsSuperManage,IsSeeOtherData,SuperRoleId,OrderByNum");
            builder.Append(")");
            builder.Append(" values (");
            builder.Append("" + model.ChainNum + ",");
            builder.Append("" + model.RoleId + ",");
            builder.Append("" + model.DeptId + ",");
            builder.Append("'" + model.LoginName + "',");
            builder.Append("'" + model.UserPass + "',");
            builder.Append("'" + model.UserName + "',");
            builder.Append("'" + model.IdCode + "',");
            builder.Append("'" + model.UserPic + "',");
            builder.Append("'" + model.CreateTime + "',");
            builder.Append("" + model.IsUseLogin + ",");
            builder.Append("" + model.IsSuperManage + ",");
            builder.Append("" + model.IsSeeOtherData + ",");
            builder.Append("" + model.SuperRoleId + ",");
            builder.Append("" + model.OrderByNum + "");
            builder.Append(")");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public void Delete(int UserId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from T_User ");
            builder.Append(" where UserId=" + UserId);
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public DataTable GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_User ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by UserId ");
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public int GetMaxId(string swhere)
        {
            return DbHelperOledb.GetMaxID(this.dbname, "UserId", "T_User", swhere);
        }

        public UserEnt GetModel(int UserId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select UserId,ChainNum,RoleId,DeptId,LoginName,UserPass,UserName,IdCode,UserPic,CreateTime,IsUseLogin,IsSuperManage,IsSeeOtherData,SuperRoleId,OrderByNum from T_User ");
            builder.Append(" where UserId=" + UserId);
            UserEnt ent = new UserEnt();
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            ent.UserId = UserId;
            if (table.Rows.Count > 0)
            {
                if (table.Rows[0]["ChainNum"].ToString() != "")
                {
                    ent.ChainNum = int.Parse(table.Rows[0]["ChainNum"].ToString());
                }
                if (table.Rows[0]["RoleId"].ToString() != "")
                {
                    ent.RoleId = int.Parse(table.Rows[0]["RoleId"].ToString());
                }
                if (table.Rows[0]["DeptId"].ToString() != "")
                {
                    ent.DeptId = int.Parse(table.Rows[0]["DeptId"].ToString());
                }
                ent.LoginName = table.Rows[0]["LoginName"].ToString();
                ent.UserPass = table.Rows[0]["UserPass"].ToString();
                ent.UserName = table.Rows[0]["UserName"].ToString();
                ent.IdCode = table.Rows[0]["IdCode"].ToString();
                ent.UserPic = table.Rows[0]["UserPic"].ToString();
                if (table.Rows[0]["CreateTime"].ToString() != "")
                {
                    ent.CreateTime = DateTime.Parse(table.Rows[0]["CreateTime"].ToString());
                }
                if (table.Rows[0]["IsUseLogin"].ToString() != "")
                {
                    ent.IsUseLogin = int.Parse(table.Rows[0]["IsUseLogin"].ToString());
                }
                if (table.Rows[0]["IsSuperManage"].ToString() != "")
                {
                    ent.IsSuperManage = int.Parse(table.Rows[0]["IsSuperManage"].ToString());
                }
                if (table.Rows[0]["IsSeeOtherData"].ToString() != "")
                {
                    ent.IsSeeOtherData = int.Parse(table.Rows[0]["IsSeeOtherData"].ToString());
                }
                if (table.Rows[0]["SuperRoleId"].ToString() != "")
                {
                    ent.SuperRoleId = int.Parse(table.Rows[0]["SuperRoleId"].ToString());
                }
                if (table.Rows[0]["OrderByNum"].ToString() != "")
                {
                    ent.OrderByNum = int.Parse(table.Rows[0]["OrderByNum"].ToString());
                }
                return ent;
            }
            return null;
        }

        public DataTable GetPageList(int PageSize, int PageIndex, string strWhere)
        {
            string orderString = "";
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_User ");
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), strWhere, orderString, out pageCount, out recordCount, "UserId");
        }

        public DataTable SearchData(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select UserId,UserName,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan and month(CreateTime)=Month(now()) and year(CreateTime)=year(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan and year(CreateTime)=year(now())) as YearMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan) as allmoney,");
            builder.Append("(select max(CreateTime) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan) as CreateTime");
            builder.Append(" from T_User ");
            if (strWhere != "")
            {
                builder.Append(" where ");
                builder.Append(strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public DataTable SearchData(int PageSize, int PageIndex, string strWhere)
        {
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select UserId,UserName,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan and month(CreateTime)=Month(now()) and year(CreateTime)=year(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan and year(CreateTime)=year(now())) as YearMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan) as allmoney,");
            builder.Append("(select max(CreateTime) from T_Procurement where t_User.UserId=T_Procurement.ProcuMan) as CreateTime");
            builder.Append(" from T_User ");
            if (strWhere != "")
            {
                builder.Append(" where ");
                builder.Append(strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), "", "$%", out pageCount, out recordCount, "UserId");
        }

        public DataTable SearchSaleData(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select UserId,UserName,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId and month(CreateTime)=Month(now()) and year(CreateTime)=year(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId and year(CreateTime)=year(now())) as YearMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId) as allmoney,");
            builder.Append("(select max(CreateTime) from T_Orders where t_User.UserId=T_Orders.UserId) as CreateTime");
            builder.Append(" from T_User ");
            if (strWhere != "")
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
                builder.Append(strWhere);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public DataTable SearchSaleData(int PageSize, int PageIndex, string strWhere)
        {
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select UserId,UserName,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId and month(CreateTime)=Month(now()) and year(CreateTime)=year(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId and year(CreateTime)=year(now())) as YearMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where t_User.UserId=T_Orders.UserId) as allmoney,");
            builder.Append("(select max(CreateTime) from T_Orders where t_User.UserId=T_Orders.UserId) as CreateTime");
            builder.Append(" from T_User ");
            if (strWhere != "")
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
                builder.Append(strWhere);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), "", "$%", out pageCount, out recordCount, "UserId");
        }

        public void Update(UserEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update T_User set ");
            builder.Append("ChainNum=" + model.ChainNum + ",");
            builder.Append("RoleId=" + model.RoleId + ",");
            builder.Append("DeptId=" + model.DeptId + ",");
            builder.Append("LoginName='" + model.LoginName + "',");
            builder.Append("UserPass='" + model.UserPass + "',");
            builder.Append("UserName='" + model.UserName + "',");
            builder.Append("IdCode='" + model.IdCode + "',");
            builder.Append("UserPic='" + model.UserPic + "',");
            builder.Append("CreateTime='" + model.CreateTime + "',");
            builder.Append("IsUseLogin=" + model.IsUseLogin + ",");
            builder.Append("IsSuperManage=" + model.IsSuperManage + ",");
            builder.Append("IsSeeOtherData=" + model.IsSeeOtherData + ",");
            builder.Append("SuperRoleId=" + model.SuperRoleId + ",");
            builder.Append("OrderByNum=" + model.OrderByNum + "");
            builder.Append(" where UserId=" + model.UserId + "");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }
    }
}

